package server.database;

import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.Objects;

import com.sun.beans.decoder.DocumentHandler;
import org.json.JSONObject;
import server.entity.*;



import org.json.JSONArray;


public class CoordDatabase{

  
    private static CoordDatabase dbInstance = null;


    public static synchronized CoordDatabase getInstance() {
        if (null == dbInstance) {
            dbInstance = new CoordDatabase();
        }
        return dbInstance;
    }

    public CoordDatabase(){

        try {
            if(ConnectionMyDataBase.dbConnection==null){
                ConnectionMyDataBase.makeConnection();
            }
            init();
        } catch (SQLException e) {
            System.out.println("Log - SQLexception");
        }

    }

    private boolean init() throws SQLException{

        if (ConnectionMyDataBase.dbConnection != null) {

            //创建表
            String createBasicDB = "CREATE TABLE IF NOT EXISTS coordinate (\n" +
                    " id integer PRIMARY KEY,\n"+
                    "time  integer,\n" +
                    "username varchar(50),\n" +
                    "longitude Double,\n" +
                    "latitude Double,\n" +
                    "description varchar(1024)\n" +
                    ");";

//            String sql = "CREATE TABLE IF NOT EXISTS employees (\n" + " id integer PRIMARY KEY,\n"
//                    + " name text NOT NULL,\n" + " capacity real\n" + ");";
            Statement createStatement = ConnectionMyDataBase.dbConnection.createStatement();
            createStatement.executeUpdate(createBasicDB);
            createStatement.close();
            //Create database if no avaiable
            return true;
        }

        System.out.println("DB creation failed");
        return false;

    }



    public void storeCoordinates(Coordinate coord) throws SQLException{

        //Code to store coordinates to database

        if(!Objects.equals(coord.getDescription(), "")){
            String setMessageString = "insert into coordinate(time,username,longitude,latitude,description) VALUES(?,?,?,?,?)";
            System.out.println(setMessageString);

            try{
                PreparedStatement pstmt = ConnectionMyDataBase.dbConnection.prepareStatement(setMessageString);

                pstmt.setLong(1, coord.getTimeAsLong());
                pstmt.setString(2, coord.getUsername());
                pstmt.setDouble(3, coord.getLatitude());
                pstmt.setDouble(4, coord.getLongitude());
                pstmt.setString(5, coord.getDescription());
                pstmt.executeUpdate();
                System.out.println("插入用户坐标成功");
            } catch (SQLException e) {
                System.out.println(e.getMessage());
                throw e;
            }
        }
        else {
            String setMessageString = "insert into coordinate(time,username,latitude,longitude) VALUES(?,?,?,?) ";

            try{
                PreparedStatement pstmt = ConnectionMyDataBase.dbConnection.prepareStatement(setMessageString);

                pstmt.setLong(1, coord.getTimeAsLong());
                pstmt.setString(2, coord.getUsername());
                pstmt.setDouble(3, coord.getLatitude());
                pstmt.setDouble(4, coord.getLongitude());
                pstmt.executeUpdate();
                System.out.println("插入用户坐标成功");
            } catch (SQLException e) {
                System.out.println(e.getMessage());
                throw e;
            }
        }



    }

    public JSONArray getAllCoordinates() throws SQLException {

        //Code to get coordinates from the database
        String sql = "SELECT * FROM coordinate";

        JSONArray jsonarray = new JSONArray();
        try {

            Statement stmt = ConnectionMyDataBase.dbConnection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int asso_id =rs.getInt("id");
                JSONArray coord =new JSONArray();
                String commentsql ="SELECT * FROM comment a where a.associated_coord_id= ?";

                PreparedStatement preparedStatement = ConnectionMyDataBase.dbConnection.prepareStatement(commentsql);
                preparedStatement.setInt(1,asso_id);
                ResultSet rs2 = preparedStatement.executeQuery();
                while (rs2.next()) {
                    String i = Instant.ofEpochMilli(rs2.getLong("time")).toString();
                    JSONObject obj2 = new JSONObject();
                    obj2.put("id",rs2.getString("id"));
                    obj2.put("comment",rs2.getString("comment"));
                    obj2.put("sent",i);
                    coord.put(obj2);
                }
                JSONObject obj = new JSONObject();
                String i = Instant.ofEpochMilli(rs.getLong("time")).toString();
                obj.put("id", rs.getString("id"));
                obj.put("username", rs.getString("username"));
                obj.put("longitude", rs.getString("longitude"));
                obj.put("latitude", rs.getString("latitude"));
                obj.put("sent", i);
                obj.put("description", rs.getString("description"));
                obj.put("comments",coord);
                jsonarray.put(obj);
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            throw  e;
        }
        return jsonarray;
    }

    public JSONArray getCoordinatesByUser(String parameter) throws SQLException {


        //Code to get coordinates from the database
        String sql = "SELECT * FROM coordinate where username = ? ";
        JSONArray jsonarray = new JSONArray();
        try {

            PreparedStatement preparedStatement = ConnectionMyDataBase.dbConnection.prepareStatement(sql);
            preparedStatement.setString(1,parameter);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                int asso_id =rs.getInt("id");
                JSONArray coord =new JSONArray();
                String commentsql ="SELECT * FROM comment a where a.associated_coord_id= ?";

                PreparedStatement preparedStatementS = ConnectionMyDataBase.dbConnection.prepareStatement(commentsql);
                preparedStatementS.setInt(1,asso_id);
                ResultSet rs2 = preparedStatementS.executeQuery();
                while (rs2.next()) {
                    String i = Instant.ofEpochMilli(rs2.getLong("time")).toString();
                    JSONObject obj2 = new JSONObject();
                    obj2.put("id",rs2.getInt("id"));
                    obj2.put("comment",rs2.getString("comment"));
                    obj2.put("sent",i);
                    coord.put(obj2);
                }
                JSONObject obj = new JSONObject();
                String i = Instant.ofEpochMilli(rs.getLong("time")).toString();
                obj.put("id", rs.getString("id"));
                obj.put("username", rs.getString("username"));
                obj.put("longitude", rs.getString("longitude"));
                obj.put("latitude", rs.getString("latitude"));
                obj.put("sent", i);
                obj.put("description", rs.getString("description"));
                obj.put("comments",coord);
                jsonarray.put(obj);
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            throw  e;
        }
        return jsonarray;


    }
    public JSONArray getCoordinatesByTime(LocalDateTime timestart, LocalDateTime timeend) throws SQLException {
//Code to get coordinates from the database

        Long time_start = timestart.toInstant(ZoneOffset.UTC).toEpochMilli();
        Long time_end = timeend.toInstant(ZoneOffset.UTC).toEpochMilli();

        String sql = "SELECT * FROM coordinate where time between ? and ?";
        JSONArray jsonarray = new JSONArray();
        try {

            PreparedStatement preparedStatement = ConnectionMyDataBase.dbConnection.prepareStatement(sql);
            preparedStatement.setLong(1,time_start);
            preparedStatement.setLong(2,time_end);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                int asso_id =rs.getInt("id");
                JSONArray coord =new JSONArray();
                String commentsql ="SELECT * FROM comment a where a.associated_coord_id= ?";

                PreparedStatement preparedStatementS = ConnectionMyDataBase.dbConnection.prepareStatement(commentsql);
                preparedStatementS.setInt(1,asso_id);
                ResultSet rs2 = preparedStatementS.executeQuery();
                while (rs2.next()) {
                    String i = Instant.ofEpochMilli(rs2.getLong("time")).toString();
                    JSONObject obj2 = new JSONObject();
                    obj2.put("id",rs2.getInt("id"));
                    obj2.put("comment",rs2.getString("comment"));
                    obj2.put("sent",i);
                    coord.put(obj2);
                }
                JSONObject obj = new JSONObject();
                String i = Instant.ofEpochMilli(rs.getLong("time")).toString();
                obj.put("id", rs.getString("id"));
                obj.put("username", rs.getString("username"));
                obj.put("longitude", rs.getString("longitude"));
                obj.put("latitude", rs.getString("latitude"));
                obj.put("sent", i);
                obj.put("description", rs.getString("description"));
                obj.put("comments",coord);
                jsonarray.put(obj);
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            throw  e;
        }
        return jsonarray;
    }
}